﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;


/*
 * Starting point
 * http://codehill.com/2009/01/reading-excel-2003-and-2007-files-using-oledb/
 * 
 * 
 */
namespace OleExcel
{
    public partial class mainForm : Form
    {
        public List<String> workSheetNames = new List<String>();   //List of Worksheet names 
        private string fileName;  //Path and file name of the Excel Workbook
        

        public mainForm()
        {
            InitializeComponent();
        }

        private void openToolStripButton_Click(object sender, EventArgs e)
        {
            if (openFileDialog.ShowDialog(this) == DialogResult.Cancel)
                return;

            fileName = openFileDialog.FileName;

                OpenExcelFile();

            //load the sheet names in the ComboBox
            sheetsToolStripComboBox.Items.Clear();
            foreach (string sheetName in workSheetNames)
            {
                if (sheetName != null)
                    sheetsToolStripComboBox.Items.Add(sheetName);
            }

            //select the first sheet in the file
            if (sheetsToolStripComboBox.Items.Count > 0)
                sheetsToolStripComboBox.SelectedIndex = 0;
        }

        /// <summary>
        /// Open the Excel Workbook
        /// </summary>
        public void OpenExcelFile()
        {
            //open the excel file using OLEDB
            OleDbConnection con;
            
            con = Markus.OleDb.fileName2OleDbConnection(fileName);
            con.Open();

            //get the number of sheets in the file
            workSheetNames = Markus.OleDb.listOfTables(con);




            if (con != null)
            {
                con.Close();
                con.Dispose();
            }
        }

        /// <summary>
        /// Reduces typing and redundant information
        /// </summary>
        /// <param name="c">Receives a parameter</param>
        /// <param name="name"></param>
        /// <param name="t"></param>
        void addParameter(OleDbCommand c, String name, OleDbType t)
        {
            // 2 hours research for the following line
            c.Parameters.Add(name, t).SourceColumn = name;
        }

        /// <summary>
        /// Returns the contents of the Ecxelworksheet as a DataTable.
        /// The DataTable may be read or written to, which is pretty cool, but only for small-medium size data.
        /// </summary>
        /// <param name="worksheetName">The worksheet's name in a string</param>
        /// <returns>A DataTable containing the data</returns>
        public System.Data.DataTable GetWorksheet(string worksheetName)
        {
            OleDbConnection _con = Markus.OleDb.fileName2OleDbConnection(fileName);
            _con.Open();

            bool exampleUpdate = false;
            if (exampleUpdate)
            {
                /*
                 * Note that you can diretcly fire SQL commands to the Excel worksheet.
                 */
                String s;
                OleDbCommand c;
                s = String.Format("update [{0}$] set [count] = 2442", worksheetName);
                c = new System.Data.OleDb.OleDbCommand(s, _con);
                c.ExecuteNonQuery();
            }

            /*
             * Mit einem Data Adapter kann ich eine Struktur in den Speicher holen
             */

            String selectString = String.Format("select * from [{0}$]", worksheetName);
            OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(selectString, _con);

            /*
             * OleDbCommandBuilder geht bei Excel nicht
             * http://support.microsoft.com/kb/316756/en-us
             * 
             * updatecommand manuell machen
             * http://msdn.microsoft.com/en-us/library/33y2221y%28v=vs.100%29.aspx
             * 
             * http://blogs.msdn.com/b/spike/archive/2008/10/29/how-to-update-an-excel-worksheet-using-dataset-and-the-oledbdataadapter.aspx
             * DataSet http://support.microsoft.com/kb/316934/en-us
             * http://www.java2s.com/Code/CSharp/Database-ADO.net/ForeachrowinDataSetreferencethecolumndatabycolumnname.htm
             * 
             */

            /*
             * There is no primary index in the source.
             * The update assumes no duplicated columns with the same A and B
             */
            String updateString = String.Format("update [{0}$] set [count] = ? where [A] = ? and [B] = ? ", worksheetName);
            da.UpdateCommand = new System.Data.OleDb.OleDbCommand(updateString, _con);
            addParameter(da.UpdateCommand, "count", OleDbType.Integer);
            addParameter(da.UpdateCommand, "A", OleDbType.VarChar);
            addParameter(da.UpdateCommand, "B", OleDbType.VarChar);


            System.Data.DataSet ds = new DataSet(); // Ein DataSet, viele Tabellen?

            // data flows from left to right
            da.Fill(ds);

            bool exampleUpdateParameter = false;
            if (exampleUpdateParameter)
            {
                int i = 2;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    dr["count"] = i++;
                    dr.EndEdit(); // unglaublich aber wahr: ohne werden keine Änderungen vermerkt.
                }
            }

            // Changes of the argument are persisted (applied to the source of the adapter).
            // data flows from right to left
            da.Update(ds);
            // was genau macht das update? Beruht das auf dem UPDATE und WHERE, also nicht auf Zellen?

            _con.Close();

            return ds.Tables[0];
        }

        private void sheetsToolStripComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            //make sure something is selected
            if (sheetsToolStripComboBox.SelectedItem == null)
                return;

            //get the contents of the selected sheet in a DataTable
            DataTable sheetContents = GetWorksheet(sheetsToolStripComboBox.SelectedItem.ToString());

            //bind the GridView to the DataTable
            if (sheetContents != null && sheetContents.Rows.Count < 1000)
                sheetDataGridView.DataSource = sheetContents;
            else
                System.Windows.Forms.MessageBox.Show("no or too many rows");


            //resize all columns' widths to fit the data
            for (int i = 0; i < sheetDataGridView.Columns.Count; i++)
            {
                sheetDataGridView.AutoResizeColumn(i, DataGridViewAutoSizeColumnMode.AllCells);
            }

        }

        private void mainForm_Load(object sender, EventArgs e)
        {

        }

    }
}
